﻿/*
1. 기본 메뉴얼
http://www.postgresql.org/docs/9.2/static/index.html
	=> 오른쪽 상단 검색어 입력하여 관련정보 검색
	=>또는 구글검색	
*/
(!!!! 쿼리 실행시 pgAdmin 에서 실행할 쿼리만 드래그 하여 F5 !!!! )
select * from member; -- recipe schema member table

/*  자료형
smallint => int2
integer => int4
date => timestamp
varchar2() => varchar()
*serial => int4 와 같으나 번호 자동증가 */
inert into board ( userid, subject,,,,) values ( 'kim','sdfd',,,);
--와 같이 idx컬럼을 생략하면 자동 증가되면서 입력됨



--<<<<< 함수 >>>>>
-- nvl(,) => coalesce(,) 같은 기능
-- sysdate => now()
-- 소수점 n자리에서 반올림 => round(값,n)

-- 게시물 입력 1000건
do $$
declare
	i int4 :=1;
begin
	while i<=1000 loop
		insert into qnaboard (idx, userid, subject, content, ref, filename, filesize) values
		( (select coalesce(max(idx), 0)+1 from qnaboard), 'groove','subject'||i,'content'||i, 
		(select coalesce(max(ref),0)+1 from qnaboard), ' ', 0);
	i = i+1;
	end loop;
end $$;

-- 페이지 나누기 쿼리( rownum )
-- from -> select -> where -> order by
-- limit : 선택할 튜플갯수,  offset : 시작포인트
select * from (
select row_number() over(order by qnaboard desc) as position, * from qnaboard 
) as qnaboard 
order by position limit 10 offset 0;

select * from (
select row_number() over(order by qnaboard desc) as position, * from qnaboard 
) as qnaboard 
where position between 1 and 10
order by position;


--!!!!!!!!!!!!!!  쿼리 모음  !!!!!!!!!!!!!!!!!

	
		select to_char(class_date,'dd') as class_date from class 
		where to_char(class_date,'yyyy-mm')='2014-01' AND state in ('0','1') order by class_date asc;



update resist set return=(select price from class where idx=3) where class_idx=3





select idx, to_char(class_date,'yy-mm-dd') class_date, state, userid, price, subject,
to_char(post_date,'yy-mm-dd') post_date, recipe_idx1, recipe_idx2,
(  (class_date-current_date<cast('7' || 'day' as interval)) AND (cnt<6 ) ) as flag from
(
select idx, class_date, state, userid, price, subject, post_date, recipe_idx1, recipe_idx2,
(select count(*) from resist where resist.class_idx=class.idx) as cnt
from class where class_date >= to_timestamp('2014-01-16','yyyy-mm-dd') AND state = '1'
order by post_date asc, class_date asc limit 30 offset 0) as list;



-- admin class list
select idx, to_char(class_date,'yy-mm-dd') class_date, state, userid, price, subject,
to_char(post_date,'yy-mm-dd') post_date, recipe_idx1, recipe_idx2, close_judgment(idx) as flag,
(select count(*) from resist where resist.class_idx=class.idx) as cnt,
(select sum(coalesce(pay,0)) from resist where class_idx=class.idx) as money
from class where class_date >= to_timestamp('2014-01-16','yyyy-mm-dd') AND state = '1'
order by post_date asc, class_date asc limit 30 offset 0;






-- groove가  오늘 이후로 개설한 강좌 목록
select idx, to_char(class_date,'yy-mm-dd') class_date, state, userid, price, subject,
(select count(*) from resist where resist.class_idx=class.idx) as cnt from class
where userid='groove' AND class_date >= now()
order by class_date asc limit 5 offset 0;
-- groove가  수강한 강좌 목록 
select idx, to_char(class_date,'yy-mm-dd') class_date, state, userid, price, subject,
(select count(*) from resist where resist.class_idx=class.idx) as cnt from class
where idx in ((select class_idx from resist where userid='groove'))
AND class_date >= now()
order by class_date asc limit 5 offset 0;



-- 내 이벤트 리스트 ( 수강신청 + 강좌개설)
select * from (
select 'resist' as type, idx, to_char(class_date,'yy-mm-dd') class_date, state,
(select count(*) from resist where resist.class_idx=class.idx) as count from class
where userid='groove' AND class_date >= now()
	UNION all
select 'class' as type, idx, to_char(class_date,'yy-mm-dd') class_date, state,
(select count(*) from resist where resist.class_idx=class.idx) as count from class
where idx in ((select class_idx from resist where userid='groove'))
AND class_date >= now()
) as myEvents order by class_date asc;





-- 강좌 개설 insert
insert into class (userid, class_date, subject, content, recipe_idx1, recipe_idx2)
values (#{userid},to_timestamp(#{class_date},'yyyy-mm-dd'),#{subject},#{content},#{recipe_idx1},#{recipe_idx2})	

-- 오늘 이후로 예약된 class_date 리스트 : 강좌 개설시 필요
select to_char(class_date,'yyyy-mm-dd') as class_date from class where class_date>now() order by class_date;

-- 요번달의 예약된 class_date list
select to_char(class_date,'dd') as class_date from class 
where to_char(class_date,'yyyy-mm')='2014-01' order by class_date asc;


--//// 레시피 리스트( +댓글수, +평점 , +레벨)
select * from (
select row_number() over(order by idx desc) as rn, * from (
select *,
(select level from member where member.userid=recipe.userid),
(select count(*) from recipe_cmt as cmt where cmt.recipe_idx=recipe.idx) count,
(select round(avg(rate),2) from recipe_cmt as cmt where cmt.recipe_idx=recipe.idx) rate
from recipe) as recipe2 ) as recipe3
where rn between 1 and 10;

-- recipe info
select idx, subject, type, ingredient, time, image, 
(select round(avg(rate),2) from recipe_cmt as cmt where cmt.recipe_idx=recipe.idx) rate
from recipe where idx=10;

-- my recipe list
select ( select coalesce(round(avg(rate),2),0) rate from recipe_cmt where recipe_idx=recipe.idx) as rate, subject, idx
from recipe where userid='admin' order by rate desc



	-- top 4 recipe by rate	
		select * from (
		select row_number() over(order by rate desc, count desc, hit desc, level asc) as rn, * from (
		select *,
		(select level from member where member.userid=recipe.userid),
		(select count(*) from recipe_cmt as cmt where cmt.recipe_idx=recipe.idx) count,
		(select coalesce(round(avg(rate),2),0) from recipe_cmt as cmt where cmt.recipe_idx=recipe.idx) rate
		from recipe) as recipe2 ) as recipe3
		where post_date>to_timestamp('2013-01-01','yy-mm-dd')
		order by rn limit 4;


--///// level manage
--1.  유저별 평점합계(각 레시피의 평점(평균)) : 유저별 레시피 등록수 가늠하기 위함
select userid, name, level, point,
( select sum(avg) from
(select idx, userid, (select avg(rate) from recipe_cmt as cmt where cmt.recipe_idx=recipe.idx) as avg from recipe) as avg2
 where avg2.userid=member.userid) as rate_sum from member;

--2. 유저별 평점평균(각 레시피의 평점(평균)) : 유저의 레시피 평가를 가늠하기 위함
select userid, name, level, point,
( select avg(avg) from
(select idx, userid, (select avg(rate) from recipe_cmt as cmt where cmt.recipe_idx=recipe.idx) as avg from recipe) as avg2
 where avg2.userid=member.userid) as rate_avg from member;

--3. levleling 하기위한 평가 요소 산출 ( 1.평점합계  2.평점평균  3.레시피등록수 ) 
select userid, name, level, point,
( select avg(avg) from
(select idx, userid, (select avg(rate) from recipe_cmt as cmt where cmt.recipe_idx=recipe.idx) as avg from recipe) as avg2
 where avg2.userid=member.userid) as rate_avg,
( select sum(avg) from
(select idx, userid, (select avg(rate) from recipe_cmt as cmt where cmt.recipe_idx=recipe.idx) as avg from recipe) as avg2
 where avg2.userid=member.userid) as rate_sum,
(select count(*) from recipe where recipe.userid=member.userid) as count,
from member 
order by rate_sum, rate_avg;

--4. 회원관리  :모든계급, 1순위 부터 30개 ,  평점펴균 내림차순 2014-01-01 이후 레시피만 계산 (0.rank 1.평점합계  2.평점평균  3.레시피등록수 ) 
select * from (
select row_number() over(order by rate_avg desc) as no, * from (
select userid, name, level, point, image,
( select coalesce(round(avg(avg),2),0) from
(select idx, userid, (select avg(rate) from recipe_cmt as cmt where cmt.recipe_idx=recipe.idx) as avg from recipe where  recipe.post_date >= to_timestamp('2014-01-01','yyyy-mm-dd') ) as avg2
 where avg2.userid=member.userid) as rate_avg,
( select coalesce(round(sum(avg),1),0) from
(select idx, userid, (select avg(rate) from recipe_cmt as cmt where cmt.recipe_idx=recipe.idx) as avg from recipe where recipe.post_date >= to_timestamp('2014-01-01','yyyy-mm-dd') ) as avg2
where avg2.userid=member.userid) as rate_sum,
(select count(*) from recipe where recipe.userid=member.userid AND recipe.post_date >= to_timestamp('2014-01-01','yyyy-mm-dd')) as count
from member) as mbList ) as mbList2
order by no limit 30 offset 0

--5. 회원관리 최종 : (0.rank 1.평점합계  2.평점평균  3.레시피등록수 ) 
select * from (
select row_number() over(order by rate_avg desc) as no, * from (
select userid, name, level, point, image,
( select coalesce(round(avg(avg),2),0) from
(select idx, userid, (select avg(rate) from recipe_cmt as cmt where cmt.recipe_idx=recipe.idx) as avg from recipe where  recipe.post_date >= to_timestamp('2014-01-01','yyyy-mm-dd') ) as avg2
 where avg2.userid=member.userid) as rate_avg,
( select coalesce(round(sum(avg),1),0) from
(select idx, userid, (select avg(rate) from recipe_cmt as cmt where cmt.recipe_idx=recipe.idx) as avg from recipe where recipe.post_date >= to_timestamp('2014-01-01','yyyy-mm-dd') ) as avg2
where avg2.userid=member.userid) as rate_sum,
(select count(*) from recipe where recipe.userid=member.userid AND recipe.post_date >= to_timestamp('2014-01-01','yyyy-mm-dd')) as count
from member where level='2') as mbList ) as mbList2
order by no limit 30 offset 0;
-- 이름 || 아이디 키워드 기반 검색 추가
select * from (
select row_number() over(order by rate_avg desc) as no, * from (
select userid, name, level, point, image,
( select coalesce(round(avg(avg),2),0) from
(select idx, userid, (select avg(rate) from recipe_cmt as cmt where cmt.recipe_idx=recipe.idx) as avg from recipe where  recipe.post_date >= to_timestamp('2014-01-01','yyyy-mm-dd') ) as avg2
 where avg2.userid=member.userid) as rate_avg,
( select coalesce(round(sum(avg),1),0) from
(select idx, userid, (select avg(rate) from recipe_cmt as cmt where cmt.recipe_idx=recipe.idx) as avg from recipe where recipe.post_date >= to_timestamp('2014-01-01','yyyy-mm-dd') ) as avg2
where avg2.userid=member.userid) as rate_sum,
(select count(*) from recipe where recipe.userid=member.userid AND recipe.post_date >= to_timestamp('2014-01-01','yyyy-mm-dd')) as count
from member where level='5' and  (userid like '%n%' or name like '%n%')) as mbList ) as mbList2
order by no limit 30 offset 0;


--/////////////  member Detail
-- *, major, rate_avg, rate_sum, recipe_cnt, cmt_cnt, favor_me_cnt, favor_rcp_cnt
select * ,
(select major from (select count(*) as cnt , type as major from recipe where recipe.userid='admin' group by type order by cnt desc limit 1) as mj),
(select round(avg(avg),2) as rate_avg from
(select idx, userid, (select avg(rate) from recipe_cmt as cmt where cmt.recipe_idx=recipe.idx) as avg from recipe) as avg2 where avg2.userid='admin'),
(select round(sum(avg),2) as rate_sum from
(select idx, userid, (select avg(rate) from recipe_cmt as cmt where cmt.recipe_idx=recipe.idx) as avg from recipe) as avg2 where avg2.userid='admin'),
(select count(*) as recipe_cnt from recipe where userid='admin'),
(select ((select count(*) from recipe_cmt where userid='admin')+(select count(*) from class_cmt where userid='admin')) as cmt_cnt),
(select count(*) as favor_me_cnt from my_member where favorid='admin'),
(select count(*) as favor_rcp_cnt from my_recipe where recipe_idx in (select idx from recipe where userid='admin'))
from member where userid='admin';


	--1.  내가 쓴 레시피중 타입 빈도수가 가장 높은 타입
	select count(*) as cnt , type as major from recipe
	where userid='admin' group by type
	order by cnt desc limit 1;
	--2.  나의 평점합계, 평점 평균
	select round(avg(avg),2) as rate_avg, round(sum(avg),1) as rate_sum  from
	(select idx, userid, (select avg(rate) from recipe_cmt as cmt where cmt.recipe_idx=recipe.idx) as avg from recipe) as avg2
	 where avg2.userid='admin';
	 --3. 내가 쓴 레시피 갯수
	select count(*) as recipe_cnt from recipe where userid='admin';
	 --4. 내가 쓴 댓글 갯수
	select (
	(select count(*) from recipe_cmt where userid='admin')+
	(select count(*) from class_cmt where userid='admin')
	) as cmt_cnt;
	--5. 내가 찜된 횟수
	select count(*) as favor_me_cnt from my_member where favorid='admin';
	--6. 내 레시피가 찜된 횟수
	select count(*) as favor_rcp_cnt from my_recipe 
	where recipe_idx in (select idx from recipe where userid='admin');


--/// 내의 활동 Top 3( 최근:post_date, subject0 | 평점: rate, subject1 | 강좌: class_date, subject2 )
	-- 1. 최근 recipe
	select to_char(post_date,'yy-mm-dd') post_date, subject from recipe where userid='admin' order by post_date desc limit 3;
	-- 2. 평점 상위 레시피
	select ( select round(avg(rate),1) rate from recipe_cmt where recipe_idx=idx), subject from recipe where userid='admin' order by rate desc limit 3;
	-- 3. 최근 개설 강좌
	select to_char(class_date,'yy-mm-dd') class_date, subject from class where userid='admin' AND class_date > now() order by class_date asc  limit 3;

-- /// 내가 찜한 회원 리스트
	select favorid, 
	(select level from member where member.userid=my_member.favorid)
	from my_member where userid='groove';
-- /// 내가 찜한 레시피 리스트
	select idx, subject, 
	(select avg(rate) from recipe_cmt as cmt where cmt.recipe_idx=recipe.idx) rate
	from recipe where idx in 
	(select recipe_idx from my_recipe where my_recipe.userid='groove');

-- ///////////// 통계 정보 /////////////////////
--1. 전체 레시피 타입별 등록 순위
select count(*) as cnt , type from recipe   group by type order by cnt desc;



--///////////// auto leveling //////////////
-- 1. 자신의 평점이 상위 몇%인지 ...
		select row_number() over( order by rate_avg desc) as rank, * from (
		select userid, level,
		( select coalesce(avg(avg),0) from
		(select idx, userid, (select avg(rate) from recipe_cmt as cmt where cmt.recipe_idx=recipe.idx) as avg from recipe) as avg2
		where avg2.userid=member.userid) as rate_avg,
		( select coalesce(sum(avg),0) from
		(select idx, userid, (select avg(rate) from recipe_cmt as cmt where cmt.recipe_idx=recipe.idx) as avg from recipe) as avg2
		where avg2.userid=member.userid) as rate_sum
		from member ) as mb where level!='0';

-- atuo level settting%
do $$
declare
	i int4 :=1;
	total int4 := (select count(*)-1 from member);
	rank cursor is
		select row_number() over( order by rate_avg desc) as rank, * from (
		select userid, level,
		( select coalesce(avg(avg),0) from
		(select idx, userid, (select avg(rate) from recipe_cmt as cmt where cmt.recipe_idx=recipe.idx) as avg from recipe) as avg2
		where avg2.userid=member.userid) as rate_avg,
		( select coalesce(sum(avg),0) from
		(select idx, userid, (select avg(rate) from recipe_cmt as cmt where cmt.recipe_idx=recipe.idx) as avg from recipe) as avg2
		where avg2.userid=member.userid) as rate_sum
		from member ) as mb where level!='0';
begin
	for rank_row in rank loop
		if rank_row.rate_sum>=50 AND (rank_row.rank/total*100)<=10 then			
			update member set level=1 where userid=rank_row.userid;
		elseif rank_row.rate_sum>=30 AND (rank_row.rank/total*100)<=30 then
			update member set level=2 where userid=rank_row.userid;
		elseif rank_row.rate_sum>=20 AND (rank_row.rank/total*100)<=70 then
			update member set level=3 where userid=rank_row.userid;
		elseif rank_row.rate_sum>=1 AND (rank_row.rank/total*100)<=100 then
			update member set level=4 where userid=rank_row.userid;
		else
			update member set level=5 where userid=rank_row.userid;		
		end if;
	end loop;
end $$;
commit;
--////////////////////////////

CREATE FUNCTION auto_level(cd_date varchar(20), cd_rate_sum1 int4,cd_rate_sum2 int4,cd_rate_sum3 int4,cd_rate_sum4 int4, cd_rate_avg1 int4,cd_rate_avg2 int4,cd_rate_avg3 int4,cd_rate_avg4 int4) 
RETURNS void AS $$
declare
	i int4 :=1;
	total int4 := (select count(*)-1 from member);
	rank cursor is
		select row_number() over( order by rate_avg desc) as rank, * from (
		select userid, level,
		( select coalesce(avg(avg),0) from
		(select idx, userid, (select avg(rate) from recipe_cmt as cmt where cmt.recipe_idx=recipe.idx) as avg from recipe where post_date>=to_timestamp(cd_date,'yyyy-mm-dd')) as avg2
		where avg2.userid=member.userid) as rate_avg,
		( select coalesce(sum(avg),0) from
		(select idx, userid, (select avg(rate) from recipe_cmt as cmt where cmt.recipe_idx=recipe.idx) as avg from recipe where post_date>=to_timestamp(cd_date,'yyyy-mm-dd')) as avg2
		where avg2.userid=member.userid) as rate_sum
		from member ) as mb where level!='0';
begin
	for rank_row in rank loop
		if rank_row.rate_sum>=cd_rate_sum1 AND (rank_row.rank/total*100)<=cd_rate_avg1 then			
			update member set level=1 where userid=rank_row.userid;
		elseif rank_row.rate_sum>=cd_rate_sum2 AND (rank_row.rank/total*100)<=cd_rate_avg2 then
			update member set level=2 where userid=rank_row.userid;
		elseif rank_row.rate_sum>=cd_rate_sum3 AND (rank_row.rank/total*100)<=cd_rate_avg3 then
			update member set level=3 where userid=rank_row.userid;
		elseif rank_row.rate_sum>=cd_rate_sum4 AND (rank_row.rank/total*100)<=cd_rate_avg4 then
			update member set level=4 where userid=rank_row.userid;
		else
			update member set level=5 where userid=rank_row.userid;		
		end if;
	end loop;
end ;
$$ LANGUAGE plpgsql;

EXECUTE auto_level('12212',30,20,10,3,10,30,50,100);
--//////////////////////////
-- 폐강 대상 정리
CREATE FUNCTION auto_class_close() 
RETURNS void AS $$
declare
	class cursor is
		select *, (select count(*) from resist where resist.class_idx=class.idx) as cnt
		from class where class_date >= current_date-30 AND state='1';
begin
	for row in class loop
		if row.cnt<6 AND (row.class_date-current_date)<cast('7' || 'day' as interval) then
			update class set state=3 where idx=row.idx;
			--update resist set pay_return=row.price where class_idx=row.idx;
		else
		end if;
	end loop;
end ;
$$ LANGUAGE plpgsql;
--/////////////////////
select (to_timestamp('2014-01-29','yyyy-mm-dd')-current_date)<cast('7' || 'day' as interval);

select cast('7' || 'day' as interval);

select (class_date-current_date)<cast('7' || 'day' as interval) from class;
--///////////////////

CREATE FUNCTION close_judgment(cls_idx int4) 
RETURNS varchar(15) AS $$
declare
	judgment varchar(15) = '';
begin
	if (select (cnt<6 AND (class_date-current_date)<cast('7' || 'day' as interval)) from (
		select idx, class_date, (select count(*) from resist where resist.class_idx=class.idx) as cnt
		from class where idx=cls_idx) as result) then
		judgment := '폐강대상';
	else
	end if;	
	RETURN judgment;
end ;
$$ LANGUAGE plpgsql;

select close_judgment(10);
-- ///////////// auto class done() /////////////
CREATE FUNCTION auto_class_done() 
RETURNS void AS $$
declare	
begin
	update class set state='2' where (class_date < current_date) AND (state='1');
end ;
$$ LANGUAGE plpgsql;



update class set state='2' where (class_date < current_date) AND (state='1')




--////////////////////////////////////////////////////////////////////////////////
﻿--<< zipcoded >

CREATE TABLE zipcode
(
  zipcode character varying(7),
  sido character varying(50),
  gugun character varying(50),
  dong character varying(52),
  bunji character varying(50)
);




-- < member >
CREATE TABLE member(
userid varchar(20) not null primary key,
passwd varchar(30) not null,
name varchar(30) not null,
email varchar(50) unique,
hp varchar(20),
zipcode varchar(7),
address1 varchar(100),
address2 varchar(100),
level char(1) default 1,
point int4 default 0,
image varchar(50),
profile varchar(300)
);
	-- insert
	insert into member values ( 'admin','1234','admin','admin@recipe.com',
	'010-1234-1234','123-123','강남','한독빌딩','0',0,'admin.jpg','admin');


-- << recipe_type >
CREATE TABLE  recipe_type(
type varchar(40) primary key
);

-- << recipe_ingredient >
CREATE TABLE  recipe_ingredient(
ingredient varchar(40) primary key
);

-- << recipe_time >
CREATE TABLE  recipe_time(
time int4 primary key
);



-- < recipe >
CREATE TABLE recipe(
idx serial primary key,
userid varchar(20) references member (userid) on update cascade,
subject varchar(100) not null,
type varchar(40) references recipe_type (type) on update cascade,
ingredient varchar(40) references recipe_ingredient (ingredient) on update cascade,
time int4 references recipe_time (time) on update cascade,
ingredients varchar(200) not null,
cooker varchar(200) not null,
hit int4 default 0,
post_date timestamp default now(),
image varchar(50) not null,
history varchar(200)
);
drop TABLE recipe.recipe;


-- << step >
CREATE TABLE step(
idx serial primary key,
recipe_idx int4 references recipe (idx) on update cascade,
step int2 not null,
important char(1) default 0,
content varchar(200) not null,
image1 varchar(50),
image2 varchar(50),
image3 varchar(50)
);
drop TABLE recipe.step;


-- << comment >
CREATE TABLE recipe_cmt(
idx serial primary key,
recipe_idx int4 references recipe (idx) on update cascade,
userid varchar(20) references member (userid) on update cascade,
rate int2,
comment varchar(200),
post_date timestamp default now()
);
drop TABLE recipe_cmt;




-- <<< my_recipe >
CREATE TABLE my_recipe(
userid varchar(20) references member (userid) on update cascade,
recipe_idx int4 references recipe (idx) on update cascade,
constraint my_recipe_pkey Primary key (userid, recipe_idx)
);
drop TABLE my_recipe;


-- <<< my_member >
CREATE TABLE my_member(
userid varchar(20) references member (userid) on update cascade,
favorid varchar(20) references member (userid) on update cascade,
constraint my_member_pkey Primary key (userid, favorid)
);


-- < class >
CREATE TABLE class(
idx serial primary key,
userid varchar(20) references member (userid) on update cascade,
class_date timestamp not null,
price int4,
recipe_idx1 int4 references recipe (idx) on update cascade,
recipe_idx2 int4 references recipe (idx) on update cascade,
hit int4 default 0,
content varchar(300),
state char(1) default 0
);


-- << order >
CREATE TABLE resist(
idx serial primary key,
userid varchar(20) references member (userid) on update cascade,
class_idx int4 references class (idx) on update cascade,
pay int4 not null,
return int4 default 0,
resist_date timestamp default now(),
rate int2,
comment varchar(200)
);



-- << class_cmt >
CREATE TABLE class_cmt(
idx serial primary key,
userid varchar(20) references member (userid) on update cascade,
class_idx int4 references class (idx) on update cascade,
post_date timestamp default now(),
content varchar(200) not null
);


-- < board >
CREATE TABLE board(
idx serial primary key,
userid varchar(20) references member (userid) on update cascade,
hit int4 default 0,
post_date timestamp default now(),
subject varchar(80) not null,
content varchar(1000) not null
);


-- << board_cmt >
CREATE TABLE board_cmt(
idx serial primary key,
board_idx int4 references board (idx) on update cascade,
userid varchar(20) references member (userid) on update cascade,
post_date timestamp default noW(),
content varchar(200) not null
);


--/////////////// dummy data insert //////////////
-- recipe
do $$
declare
	i int4 :=1;
begin
	while i<=100 loop
		if i%3 = 1 then
			insert into recipe (userid,subject,type,ingredient,time,ingredients,cooker,image,history) values
			('groove','돈까스','일식','돼지고기','30','튀김가루,식용유,돈까스소스','튀김솥','dongas.jpg','바삭바삭 맛있는 돈까스');
		elseif i%3 =2 then
			insert into recipe (userid,subject,type,ingredient,time,ingredients,cooker,image,history) values
			('kmj','유부초밥','일식','쌀','15','유부등 각종재료,식초','손가락','yubu.jpg','엄마가 소풍갈 때 마다 싸줌..');
		else
			insert into recipe (userid,subject,type,ingredient,time,ingredients,cooker,image,history) values
			('admin','탕수육','중식','돼지고기','60','부침가루,케첩,설탕,당근,식용유','기름솥,뜰채','tangsu.jpg','맛이 끝내주는 탕슉');
		end if;
	i = i+1;
	end loop;
end $$;

-- recipe_cmt
delete from recipe_cmt;
do $$
declare
	i int4 :=1;
begin
	while i<=1000 loop
		if i%5 = 1 then
			insert into recipe_cmt (recipe_idx,userid,rate,comment) values
			(i%100+1,'bjh',i%5,i||'별로 모르겠네요...');
		elseif i%5 =2 then
			insert into recipe_cmt (recipe_idx,userid,rate,comment) values
			(i%100+1,'ajg',i%5,i||'맛있네요!!!');
		elseif i%5 =3 then
			insert into recipe_cmt (recipe_idx,userid,rate,comment) values
			(i%100+1,'gr',i%5,i||'완전 안이뻐!');
		elseif i%5 =4 then
			insert into recipe_cmt (recipe_idx,userid,rate,comment) values
			(i%100+1,'kbk',i%5,i||'...;;;,,,,');
		else
			insert into recipe_cmt (recipe_idx,userid,rate,comment) values
			(i%100+1,'admin',i%5,i||'good good good !!!');
		end if;
	i = i+1;
	end loop;
end $$;


-- member
do $$
declare
	i int4 :=1;
begin
	while i<=100 loop
		if i%3 = 1 then
			insert into member (userid,passwd,name,email,hp,zipcode,address1,level,image,profile) values
			('sist'||i,'1234','sist'||i,i||'sist@recipe.com','123-456-789'||i,'566-777','서울시 강남구 '||i||'번지',3,'sist.jpg','sist'||i);
		elseif i%3 =2 then
			insert into member (userid,passwd,name,email,hp,zipcode,address1,level,image,profile) values
			('chef'||i,'1234','chef'||i,i||'chef@recipe.com','567-456-789'||i,'678-577','서울시 강남구 '||i||'번지',2,'chef.jpg','chef'||i);
		else
			insert into member (userid,passwd,name,email,hp,zipcode,address1,level,image,profile) values
			('newbie'||i,'1234','newbie'||i,i||'newbie@recipe.com','567-111-789'||i,'111-077','서울시 강남구 '||i||'번지',5,'newbie.jpg','newbie'||i);
		end if;
	i = i+1;
	end loop;
end $$;


-- board
do $$
declare
	i int4 :=1;
begin
	while i<=100 loop
		if i%3 = 1 then
			insert into board (userid,subject,content) values
			('admin','test_subject'||i,'test_content'||i);
		elseif i%3 =2 then
			insert into board (userid,subject,content) values
			('groove','groove_subject'||i,'groove_content'||i);
		else
			insert into board (userid,subject,content) values
			('bjh','chef_subject'||i,'chef_content'||i);
		end if;
	i = i+1;
	end loop;
end $$;

-- class
do $$
declare
	i int4 :=1;
begin
	while i<=30 loop
		if i%3 = 1 then
			insert into class (userid,class_date,price,recipe_idx1,recipe_idx2,content,state,post_date, subject) values
			('admin',to_timestamp('2014-01-'||i,'yyyy-mm-dd'),20000,i+13,i+20,'Best Recipe Class '||i,'1',now(),'Best Recipe Class '||i);
		elseif i%3 =2 then
			insert into class (userid,class_date,price,recipe_idx1,recipe_idx2,content,state,post_date, subject) values
			('chef29',to_timestamp('2014-01-'||i,'yyyy-mm-dd'),null,i+14,i+21,'Worst Recipe Class '||i,'0',now(),'Worst Recipe Class '||i);
		else
			insert into class (userid,class_date,price,recipe_idx1,recipe_idx2,content,state,post_date, subject) values
			('kbk',to_timestamp('2014-01-'||i,'yyyy-mm-dd'),25000,i+15,i+22,'Null Recipe Class '||i,'2',now(),'Null Recipe Class '||i);
		end if;
	i = i+1;
	end loop;
end $$;




--/////////////////////////////////////////


	